USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH
    Sales_CTE
    (
        SalesPersonID
        ,SalesOrderID
        ,SalesYear
    )
    AS
    -- Define the CTE query.
    (
        SELECT
            SalesPersonID
            ,SalesOrderID
            ,YEAR(OrderDate) AS SalesYear
        FROM
            Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
-- Define the outer query referencing the CTE name.
SELECT
    SalesPersonID
    ,COUNT(SalesOrderID) AS TotalSales
    ,SalesYear
FROM
    Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

